package com.lianbi.dao;

import com.lianbi.conn.ConnectionPool;
import com.mongodb.BasicDBObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TaskWorkDao {
	
	private static ConnectionPool pool = null;
	private static Connection conn = null;
	private static PreparedStatement stmt = null;
	private static ResultSet rs = null;
	
	public void initPool() {
		pool = ConnectionPool.getInstance();
		conn = pool.getConnection();
	}

	/**
	 * 通过资讯类型查询总条数
	 * 
	 * @param sign  类型
	 * 
	 * */
	public int getCountBySign(String sign,String signTypeId) {
		String listCountSql = "select count(id) from t_affiche where is_enable='Y' and sign = ? ";
		if (signTypeId != null && !"".equals(signTypeId)) {
			listCountSql = listCountSql + " and sign_type = ? ";
		}
		int count = 0;
		
		try {
			stmt = conn.prepareStatement(listCountSql);
			stmt.setString(1, sign);
			if (signTypeId != null && !"".equals(signTypeId)) {
				stmt.setString(2, signTypeId);
			}
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
				System.out.println(count);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}
        
		return count;
	}

	/**
	 * 通过标签类型查询总条数
	 *
	 * @param sign  类型
	 *
	 * */
	public int getCountByLabel(String sign,String signTypeId) {
		String listCountSql = "select count(id) from t_affiche where is_enable='Y' and sign = ? and instr(label, ?) > 0";
		int count = 0;

		try {
			stmt = conn.prepareStatement(listCountSql);
			stmt.setString(1, sign);
			stmt.setString(2, signTypeId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
				System.out.println(count);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}

		return count;
	}
	
	/**
	 * 查询分页list
	 * 
	 * @param sign   类型
	 * @param start  起始数据
	 * @param end    截止数据
	 * 
	 * */
	public List<BasicDBObject> getListBySign(String sign, String signType, int start, int end) {
		String listSql = "SELECT * FROM ( SELECT A.*, ROWNUM RN "
        		+ "FROM (select row_number() over(partition by t.sign_type order by (select sort from t_lbbq_manange bq where bq.id = t.sign_type) asc)," +
				"        t.sign_type," +
				"        (select lbbq_name from t_lbbq_manange where id = t.sign_type) signTypeName," +
				"        t.id," +
				"        t.sort," +
				"        t.title," +
				"        t.affiche_content," +
				"        t.description," +
				"        t.keywords," +
				"        t.small_icon_url," +
				"        t.html_url," +
				"        t.label," +
				"        t.read_times," +
				"        to_char(t.update_time,'yyyy-MM-dd hh24:mi:ss') update_time" +
				"        from t_affiche t where is_enable='Y' and sign = ? ";

		if (signType != null && !"".equals(signType)) {
			listSql = listSql + " and sign_type = ? ";
		}
		listSql = listSql +"order by sort,t.update_time desc) A WHERE ROWNUM <= ? ) WHERE RN >= ?";
		List<BasicDBObject> list = new ArrayList<BasicDBObject>();
		BasicDBObject dataObject = null;
		
		try {
			stmt = conn.prepareStatement(listSql);
			stmt.setString(1, sign);
			if (signType != null && !"".equals(signType)) {
				stmt.setString(2, signType);
				stmt.setInt(3, end);
				stmt.setInt(4, start);
			} else {
				stmt.setInt(2, end);
				stmt.setInt(3, start);
			}
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				dataObject = new BasicDBObject();
				dataObject.put("id", rs.getString("id")); 
				dataObject.put("afficheContent", rs.getString("affiche_content"));
				dataObject.put("title", rs.getString("title"));
				dataObject.put("signType", rs.getString("sign_type")==null?"":rs.getString("sign_type"));
				dataObject.put("signTypeName", rs.getString("signTypeName"));
				dataObject.put("smallIconUrl", rs.getString("small_icon_url"));
				dataObject.put("htmlUrl", rs.getString("html_url"));
				dataObject.put("label", rs.getString("label")==null?"":rs.getString("label"));
				dataObject.put("description", rs.getString("description")==null?"":rs.getString("description"));
				dataObject.put("keywords", rs.getString("keywords")==null?"":rs.getString("keywords"));
				dataObject.put("updateTime", rs.getString("update_time"));
				dataObject.put("readTimes", rs.getString("read_times")==null?"":rs.getString("read_times"));
				list.add(dataObject);
			}
			
			System.out.println(list.size());
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}
        
		return list;
	}

	/**
	 * 查询分页list
	 *
	 * @param sign   类型
	 * @param start  起始数据
	 * @param end    截止数据
	 *
	 * */
	public List<BasicDBObject> getListByLabel(String sign, String signType, int start, int end) {
		String listSql = "SELECT * FROM ( SELECT A.*, ROWNUM RN "
				+ "FROM (select " +
				"        t.sign_type," +
				"        '全部' signTypeName," +
				"        t.id," +
				"        t.sort," +
				"        t.title," +
				"        t.affiche_content," +
				"        t.description," +
				"        t.keywords," +
				"        t.small_icon_url," +
				"        t.html_url," +
				"        t.label," +
				"        t.read_times," +
				"        to_char(t.update_time,'yyyy-MM-dd hh24:mi:ss') update_time" +
				"        from t_affiche t where is_enable='Y' and sign = ? and instr(t.label, ?) > 0";

		listSql = listSql +"order by t.sort,t.update_time desc) A WHERE ROWNUM <= ? ) WHERE RN >= ?";
		List<BasicDBObject> list = new ArrayList<BasicDBObject>();
		BasicDBObject dataObject = null;

		try {
			stmt = conn.prepareStatement(listSql);
			stmt.setString(1, sign);
			stmt.setString(2, signType);
			stmt.setInt(3, end);
			stmt.setInt(4, start);
			rs = stmt.executeQuery();

			while (rs.next()) {
				dataObject = new BasicDBObject();
				dataObject.put("id", rs.getString("id"));
				dataObject.put("afficheContent", rs.getString("affiche_content"));
				dataObject.put("title", rs.getString("title"));
				dataObject.put("signType", rs.getString("sign_type"));
				dataObject.put("signTypeName", rs.getString("signTypeName"));
				dataObject.put("smallIconUrl", rs.getString("small_icon_url"));
				dataObject.put("htmlUrl", rs.getString("html_url"));
				dataObject.put("label", rs.getString("label"));
				dataObject.put("description", rs.getString("description")==null?"":rs.getString("description"));
				dataObject.put("keywords", rs.getString("keywords")==null?"":rs.getString("keywords"));
				dataObject.put("updateTime", rs.getString("update_time"));
				dataObject.put("readTimes", rs.getString("read_times")==null?"":rs.getString("read_times"));
				list.add(dataObject);
			}

			System.out.println(list.size());
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * 通过Id查询详细内容
	 * 
	 * @param id   文章id
	 * 
	 * */
	public BasicDBObject getContentById(int id) {
		String contentSql = "select t.id," +
				"       t.affiche_content," +
				"       t.title," +
				"       t.sign," +
				"       (select lbbq_name from t_lbbq_manange bq where bq.id = t.sign_type) lbbq_name," +
				"       t.small_icon_url," +
				"       t.intro," +
				"       t.html_url," +
				"       t.description," +
				"       t.keywords," +
				"       t.read_times," +
				"       to_char(t.update_time, 'yyyy-MM-dd hh24:mi:ss') update_time" +
				"  from t_affiche t where id = ? ";
		BasicDBObject dataObject = new BasicDBObject();
		
		try {
			
			stmt = conn.prepareStatement(contentSql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				dataObject.put("id", rs.getString("id")); 
				dataObject.put("afficheContent", rs.getString("affiche_content"));
				dataObject.put("title", rs.getString("title"));
				dataObject.put("sign", rs.getString("sign"));
				dataObject.put("signTypeName", rs.getString("lbbq_name"));
				dataObject.put("smallIconUrl", rs.getString("small_icon_url"));
				dataObject.put("intro", rs.getString("intro"));
				dataObject.put("htmlUrl", rs.getString("html_url"));
				dataObject.put("description", rs.getString("description")==null?"":rs.getString("description"));
				dataObject.put("keywords", rs.getString("keywords")==null?"":rs.getString("keywords"));
				dataObject.put("readTimes", rs.getString("read_times"));
				dataObject.put("updateTime", rs.getString("update_time"));
			}
			System.out.println(dataObject.getString("afficheContent"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}
        
		return dataObject;
	}
	
	/**
	 * 关闭数据库连接
	 * 
	 * */
	public void close() {
		try {
			if(rs != null) {
				rs.close();
			}
			if(stmt != null) {
				stmt.close();
			}
			if(pool != null) {
				pool.release(conn);
			   // pool.closePool();
			}
		} catch (SQLException e) {
			System.out.println("数据库关闭失败");
			e.printStackTrace();
		}
	}

	/**
	 * 根据资讯类别查询列表
	 * @param type
	 * @param bqType
	 * @return
	 */
	public List<BasicDBObject> getSignTypeList(String type,String bqType) {
		String signTypeSql= "select distinct(b.id),b.lbbq_name from t_lbbq_manange b  ";
		if("2".equals(type)) {
			signTypeSql = signTypeSql + "inner join t_affiche t on instr(t.label, b.id) > 0 ";
		}
		if("1".equals(type)) {
            signTypeSql = signTypeSql + "inner join t_affiche t on t.sign_type=b.id ";
        }
		signTypeSql = signTypeSql + "where b.type= ?";
		if (bqType != null && !"".equals(bqType)) {
			signTypeSql = signTypeSql + " and bq_type = ? ";
		}
 		List<BasicDBObject> signTypeList = new ArrayList<>();
		BasicDBObject dataObject = null;
		try {
			stmt = conn.prepareStatement(signTypeSql);
			stmt.setString(1, type);
			if (bqType != null && !"".equals(bqType)) {
				stmt.setString(2, bqType);
			}
			rs = stmt.executeQuery();
			while (rs.next()) {
				dataObject = new BasicDBObject();
				dataObject.put("lbbqId", rs.getString("id"));
				dataObject.put("lbbqName", rs.getString("lbbq_name"));
				signTypeList.add(dataObject);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}

		return signTypeList;
	}

	/**
	 * 查询友情链接列表
	 * @param type
	 * @return
	 */
	public List<BasicDBObject> getFrientLinkList(String type) {
		String frientLinkSql= "select t.company_name,t.company_url from t_website_manage t where t.is_enable='Y' and t.type= ?";
		List<BasicDBObject> frientLinkList = new ArrayList<>();
		BasicDBObject dataObject = null;
		try {
			stmt = conn.prepareStatement(frientLinkSql);
			stmt.setString(1, type);
			rs = stmt.executeQuery();
			while (rs.next()) {
				dataObject = new BasicDBObject();
				dataObject.put("companyName", rs.getString("company_name"));
				dataObject.put("companyUrl", rs.getString("company_url"));
				frientLinkList.add(dataObject);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}

		return frientLinkList;
	}

	/**
	 * 根据标签查询资讯列表
	 * @param label
	 * @return
	 */
	public List<BasicDBObject> getLabelList(String label) {
		String labelSql= "select t.id,t.lbbq_name from t_lbbq_manange t where id in (" +label+
				")";
		List<BasicDBObject> labelList = new ArrayList<>();
		BasicDBObject dataObject = null;
		try {
			stmt = conn.prepareStatement(labelSql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				dataObject = new BasicDBObject();
				dataObject.put("labelId", rs.getString("id"));
				dataObject.put("labelName", rs.getString("lbbq_name"));
				labelList.add(dataObject);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				System.out.println("关闭失败");
				e.printStackTrace();
			}
		}

		return labelList;
	}
}
